Charlie Calvert's C++ Builder Unleashed - 11 - Working with Field Objects This chapter covers a set of visual tools you can use to simplify database development. The major areas covered are as follows: The Fields Editor TField descendant objects Calculated fields The TDBGrid component Lookup fields MultiRecord objects--TDBCtrlGrid These subjects are all related to databases, and to the TField and TDBGrid objects in particular. BCB is a very sophisticated database tool, and it takes time to get a feeling for the breadth and depth of the tools it provides for client/server developers. One of the goals of this chapter is to give you some sense of the powerful TField object and its impact on designing database applications. One of the most frequently mentioned tools in this chapter is the Fields Editor. By using the Fields Editor, you can create objects that influence the appearance of data shown in visual controls such as TDBEdit and TDBGrid. For instance, you can use the objects made in the Fields Editor to format data so that it appears as currency or as a floating-point number with a defined precision. These same changes can be accomplished through the Data Dictionary in the Database Explorer or through the Database Desktop. These latter tools, however, have a global impact on the field's potential values, whereas the changes made in the Object Inspector affect only the current application. If you want to dynamically emphasize a particular column or row in a table, this is the chapter you should read to get the information you need. For instance, this chapter covers the Columns property of the TDBGrid control. The Columns property can be used to change the appearance of a grid so that its columns are arranged in a new order or are hidden. You can also use the Columns property to change the color of columns in a grid, or to insert drop-down combo boxes into a grid. The lessons you learn in this chapter will arm you with key techniques used by most pro- grammers when they present database tables to their users. Much of the material involves manipulating visual tools, but the basic subject matter is fairly technical and assumes a basic understanding of the BCB environment and language. Note that third parties such as TurboPower (www.turbopower.com) and Woll2Woll (www.woll2woll.com) have grid objects that go beyond the capability of the native grids that ship with BCB. The Fields Editor To get things rolling, it might be a good idea to take a close look at the invaluable Fields Editor. This tool lies very much at the heart of BCB database programming. To access it, you double-click or right-click on a TTable or TQuery object. The Fields Editor helps you associate custom objects with some or all of the fields from a table. By associating a custom object with a field, you can control the way a field displays, formats, validates, and inputs data. The Fields Editor also enables you to add new fields to a table at runtime and then to calculate the values that will be shown in the new fields. This latter procedure is referred to as calculated fields. Another benefit of the Fields Editor is that it helps you create lookup fields. Lookup fields perform a lookup from one table into another based on a foreign key. It's important to understand that the Fields Editor is just a utility. Everything done inside the Fields Editor could be done in code, although in this particular case the code in question becomes a little complicated, or at least time consuming. In this section and the next, you will be building a program called MASS, which illustrates both the Fields Editor and calculated fields. This is an important program, so you should try to use your copy of BCB to follow the steps described. Getting Started with the Fields Editor You can access the Fields Editor from either a TTable or TQuery object. To get started, drop a TQuery object on a data module, set up the BCDEMOS alias, enter the SQL statement select * from animals, and make the table active. (The BCDEMOS alias is created automatically when you install BCB. It points at the data in the CBuilder/Examples/Data directory. If you are having trouble with aliases, see the readme file on the CD or visit my Web site.) Drop down the Object Selector at the top of the Object Inspector. Notice that you currently have two components in use: TDataModule and TQuery. Right-click the TQuery object and select the Fields Editor menu choice to bring up the Fields Editor. Right-click the Fields Editor and select Add Fields from the menu to pop up the Add Fields dialog, as shown in Figure 11.1. FIGURE 11.1. The Add Fields dialog box from the Fields Editor. By default, all of the fields in the dialog box are selected. Click the OK button to select all five fields, and then close the Fields Editor. Open the Object Selector a second time; notice that there are now five new objects on your form, as shown in Figure 11.2. FIGURE 11.2. The Object Selector lists the objects created in the Fields Editor. You also can find this list in the TForm1 class definition. These objects help you hone and define your presentation of the Animals table to the user. Here's a complete list of the objects you just created, as they appear in your header file: TStringField *AnimalsQueryNAME; TSmallintField *AnimalsQuerySIZE; TSmallintField *AnimalsQueryWEIGHT; TStringField *AnimalsQueryAREA; TBlobField *AnimalsQueryBMP; The origins of the names shown here should be fairly obvious. The string AnimalsQuery comes from the name I've given to the TQuery object, and the second half of the name comes from the fields in the Animals table. This naming convention I've adopted here can be very useful if you are working with several tables and want to know at a glance which table and field are being referenced by a particular variable. NOTE: The names of the fields in the example shown here are capitalized only because the table in question is a dBASE table. dBASE tables automatically capitalize all letters in field names. If I had chosen to work with some other type of table, the capitalization of the letters in the field name would have followed the rules defined by the current database software. This principle appears again and again in BCB database programming. Whenever possible, the actual naming conventions and other traits of a server are surfaced inside of BCB. The developers of C++Builder did not aim to make all servers appear the same to you, but rather to give you access to their features as transparently as possible. Of course, generic tools such as the TTable object are also common in BCB. The VCL team, however, strove to find the right balance between expediting access to the database and unintentionally crippling the database by wrapping too many of its features inside objects. As a rule, the developers did whatever they could to ease the path, without ever going so far as to actually cut you off from a feature of the server you are accessing. This same principle applies to the VCL. The developers tried to do everything they could to make Windows programming simple, but they drew the line if a particular feature was so warm and fuzzy that it cut you off from direct access to the OS when and if you needed it. Each of the objects created in the Fields Editor is a descendant of TField. The exact type of descendant depends on the type of data in a particular field. For instance, the AnimalsQueryWEIGHT field is of type TSmallIntField, whereas the AnimalsQueryNAME field is of type TStringField. These are the two field types you will see most often. Other common types include TDateField and TCurrencyField, neither of which are used in this particular table. Remember that these types were selected to correspond with the field types in the table itself. TStringField, TSmallIntField, and the other objects shown here are all descendants of TField and share its traits. If you want, you can treat these objects exactly as you did the TField objects that you learned about in Chapter 9, "Using TTable and TDataSet." For instance, you can write this: S = MyIntegerField->AsString; and this: S = MyIntegerField->Name; However, these descendants of TField are very smart objects and have several traits that go beyond the functionality of their common ancestor. The most important property you will see is called Value. You can access it like this: void __fastcall TForm1::Button1Click(TObject *Sender) { int i; AnsiString S; DMod->AnimalsQuery->Edit(); i = DMod->AnimalsQuerySIZE->Value; S = DMod->AnimalsQueryNAME->Value; i += 1; S = "Foo"; DMod->AnimalsQuerySIZE->Value = i; DMod->AnimalsQueryNAME->Value = S; } The code shown here first assigns values to the variables i and S. The next two lines change these values, and the last two lines reassign the new values to the objects. It usually wouldn't make much sense to write code exactly like this in a program, but it serves to illustrate the syntax used by TField descendants. (If you bother to try to write code like this as a test, remember that the RequestLive property for a TQuery object is set to False by default. You would have to set it to True before the code would work.) The Value property always conforms to the type of field you have instantiated. For instance, TStringFields are strings, whereas TCurrencyFields always return floating-point double values. However, if you show a TCurrencyField in a data-aware control, it will return a string that looks like this: "$5.00". The dollar sign and the rounding to decimal places are simply part and parcel of what a TCurrencyField is all about. The preceding example might make you think that these variables are declared as Variants, which indeed is the case for the TField object itself. In the actual implementation, however, the TCurrencyField->Value is declared as a Double. If you tried to assign a string to it, you would get a type mismatch. Likewise, TIntegerField.Value is declared as a LongInt, and so on. TSmallIntField and TWordField are both descendants of TIntegerField and inherit the Value declaration as a LongInt. However, they have other internal code that affects the Value field, just as TCurrencyField rings some changes on its Value field to make it look like a monetary value. If you have the source, look up DBTABLES.PAS and DB.PAS to find the details of these constructions. (The Pascal source ships with some versions of BCB.) At any rate, the point here is that the preceding code is an example of polymorphism; it is not an example of relaxed type-checking. The Value field has a specific type--it's just that it undergoes polymorphic changes. If you want the names of each field in the current dataset, you should reference the FieldName property through one of the following two methods: S = AnimalsQuery->Fields[0]->FieldName; S = AnimalsQueryNAME.FieldName; If you want the name of an object associated with a field, you should use the Name property: S = AnimalsQuery->Fields[0]->Name; S = AnimalsQueryNAME->Name; When using the ANIMALS table, the first two examples shown previously yield the string "Name", while the second two lines yield "Query1NAME". Special properties are associated with most of the major field types. For instance, TIntegerFields have DisplayFormat and DisplayEdit properties, as well as MinValue and MaxValue properties. TStringFields, on the other hand, have none of these properties, but they do have an EditMask property, which works just like the TEditMask component found on the Additional page of the Component Palette. All these properties are used to control the way data is displayed to the user, or the way that input from the user should be handled. NOTE: I don't want to get ahead of myself, but properties such as MinValue and MaxValue are also used in the Data Dictionary, as will be explained later in this chapter. Changes made in the Data Dictionary will affect these values as seen in the Object Inspector, but changes in the Object Inspector will not affect the Data Dictionary. Don't worry if this doesn't make the slightest bit of sense yet, as I will get to the Data Dictionary in just a little while. Calculated Fields You should be aware of one more thing about the Fields Editor. You can use this tool not only to build objects that encapsulate existing fields, but also to build objects that represent new fields. For instance, suppose you wanted to create a sixth field, Mass, which contains the product of the SIZE and WEIGHT fields, in the Animals table. To create the Mass field, open the Fields Editor again, right-click it, and select the New Field menu choice. In the top part of the New Field dialog, enter the word Mass. Now set its type to Integer, and leave its field type as Calculated, as shown in Figure 11.3. FIGURE 11.3. Creating the Mass field in the Fields Editor. If you close the Fields Editor and add a TDataSource and TDBGrid to your project, you will see that the Animals table now appears to have six fields, the last of which is called MASS. Of course, it's one thing to create a field, and another to fill it in at runtime with an appropriate value. The act of placing a value in the new field you have created involves a concept called calculated fields. Calculated fields are one of the most valuable features of the TField object and its related architecture. You can use these calculated fields for several different purposes, but two stand out: If you need to perform calculations on two or more of the fields in a dataset and want to show the results of the calculations in a third field, you can use calculated fields. A scenario describing this type of situation was set up at the beginning of this section. If you are viewing one dataset and want to perform calculations or display data that involve lookups in at least one additional dataset, you can use the Fields Editor and calculated fields to show the results of these calculations in a new field of the first dataset. There is also a second, much better method for doing lookups. I will talk about that method later in this chapter. As a rule, you should do calculations in calculated fields, and lookups in lookup fields, though calculated fields are powerful enough to fill a number of different roles in your programs. The MASS program illustrates one example of the first of the two uses for calculated fields. You got this program started in the last section when you created the field called MASS and displayed it in a grid. To continue working with the MASS program, highlight the AnimalsQuery object and set the Object Inspector to the Events page. Now create an OnCalcFields event that looks like this: void __fastcall TDMod::AnimalsQueryCalcFields(TDataSet *DataSet) { AnimalsQueryMass->AsInteger = AnimalsQuerySIZE->AsInteger * AnimalsQueryWEIGHT->AsInteger; } The code shown here assigns the value of the AnimalsQueryMass object to the product of the AnimalsQuerySIZE and sqlWeightWEIGHT fields. This kind of multiplication is legal to do because all of the fields are of the same type. Furthermore, you could have used the Value property instead of AsInteger. I explicitly declared the type in this example to help illustrate precisely what is going on. OnCalcField methods are called each time a record is displayed to the user. As a result, all of the Mass fields displayed in the grid are properly filled in, as shown in Figure 11.4. FIGURE 11.4. The MASS field contains the product of the WEIGHT and SIZE fields. A TDBImage control contains a bitmap from the BMP field of the table. To get the screen shot shown in Figure 11.4, I opened the Column property in the TDBGrid object and selected Add All Fields. I then deleted the Area and BMP fields and closed the Column property editor. I will talk more about the grid object later in this chapter. If you choose to never instantiate a particular field in the Fields Editor, the current dataset you are working with no longer contains that field. It can't be accessed programmatically or visually at runtime. Usually, this is exactly the effect you want to achieve, and so this trait will generally be perceived as a strong benefit. However, there are times when it might not serve your purposes, and in those cases you should either create an object for all the fields in a table or stay away from the Fields Editor altogether. Remember that you can hide fields inside a grid by using the Column property, as shown previously. That way, you create objects for all fields, but show only certain ones to the user. Lookup Fields You can use lookup fields to look up a value in one table that you want to use in a second table. For instance, suppose you had two tables, one of which contained a list of books, and the other contained a list of authors. It would be nice if you could automatically view a list of the existing authors whenever you needed to add a new book to the Books table. That way, you could enter the book's name, look up the author in a drop-down list, and presto, you would be done. The Books table would then automatically contain a reference to the appropriate author in the Authors table. That is, the author number from the Authors table would automatically be inserted in the Books table. Another way to think about lookup fields is that they provide the ability to perform a powerful kind of pseudo-join using the TTable object. Suppose two tables called Authors and Books are related on a field called AuthNo. AuthNo is the primary key of the Authors table, and it is a foreign key in the Books table. When you are looking at the Books table, sometimes you would like to be able to include the name of the author of each book inside the book table. That is, you would like to perform a join on the book and author table. You can't actually perform a join, however, because you are using the TTable object, and not TQuery. The solution to this dilemma is the lookup field. It will use the foreign key in the Books table to reference the name of the author from the Author table. This technique does join one better, however, because it will let you not only view a field from the Authors table as if it were part of the Books table, but also enables you to drop down a list of all the authors in the Authors table while you are still viewing the Books table, as shown in Figure 11.5. In short, lookup fields give you the same type of benefits you derive from performing a join between two tables. In particular, they let you combine the fields of two tables so that you can create one dataset with fields from multiple tables. NOTE: Lookup fields are a bit like a combination of a one-to-many relationship and a calculated field. The techniques used to actually implement them, however, have more in common with calculated fields than they do with one-to-many relationships. There are significant differences between the three technologies, but I still tend to think of calculated fields, lookup fields, and one-to-many relationships as being interrelated concepts. Because lookup fields are so much like one-to-many relationships, it is usually not a good idea to use both techniques simultaneously with the same two TTable objects. For instance, if you have the Authors table related to the books table in a one-to-many, you wouldn't want to simultaneously do a lookup from the Books table to the author table. This problem, and its solution, are addressed in the Lookup example on the CD-ROM that accompanies this book. That program will be discussed throughout the rest of this section of the chapter. I should perhaps add that lookup fields are a great technique to use with relatively small datasets. If you are from the world of big iron, and work with tables that contain tens of thousands of records or more, you will probably find lookup fields are of only limited use to you. Needless to say, BCB gives good support for using lookup fields. You can now perform automatic lookups inside grids, list boxes, and combo boxes. In particular, the following controls support lookups: TDBGrid, TDBCtrlGrid, TDBLookupListBox, and TDBLookupComboBox. The Lookup program shows how to proceed. The code for this application is shown in Listings 11.1 through 11.3. Two views of the program are shown in Figures 11.5 and 11.6. FIGURE 11.5. The main form for the Lookup program. FIGURE 11.6. This form features a combo box that lets you perform lookups from the Books table into the Authors table. NOTE: The first version of Delphi had a TDBLookupCombo control and a TDBLookupList control that had certain limited capabilities. Both of these controls are still present in some versions of BCB, but they have been moved off the Data Controls page onto the Win 3.1 page. They are being kept around solely for compatibility with legacy Pascal code, and you should not use them in new programs. The TDBLookupComboBox control and the TDBLookupListBox control now replace the old 16-bit controls, and they outperform them on several fronts. In particular, the TDBLookupComboBox and TDBLookupListBox will be filled up automatically with the data from the lookup table. Don't confuse the old control with the new ones! TDBLookupComboBox is the fancy one; the TDBLookupCombo is the old-fashioned one. You might use the following somewhat whimsical mnemonic: The TDBLookupListBox has a bigger name than the TDBLookupList because it has "bigger" capabilities. By the way, this is a classic example of why it is important to get things right the first time. In particular, it shows why it is sometimes better to cut a feature rather than trying to put in a hack that you will want to improve in later versions. In particular, the TDBLookupCombo was poorly implemented in the first version of Delphi, which was a 16-bit program. Because Delphi 2.0 promised to compile all your 16-bit programs, this component had to be left in the product even though it was replaced with a far superior tool. Now, this old nemesis lives on even in the C++ version of the product, because BCB advertises the fact that it supports all the legacy Pascal code you might want to bring into a project. Here's the summary: The original error was made back in Delphi 1.0, but the repercussions still echo even when the 32-bit version of the Delphi is ported to C++! Clearly, it is worthwhile making sure that things are designed right the first time, or else they should be left out of the product altogether. Of course, this is a rule that can be stated fairly easily, but is difficult to live up to. Listing 11.1. The core functionality for the Lookup program is done in the Object Inspector for the TDMod object and not here in the code for Dmod1.cpp. /////////////////////////////////////// // File: DMod1.cpp // Project: Lookup // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "DMod1.h" #pragma resource "*.dfm" TDMod *DMod; __fastcall TDMod::TDMod(TComponent* Owner) : TDataModule(Owner) { AuthorTable->Open(); BookDetailTable->Open(); BookLookupTable->Open(); } void __fastcall TDMod::AuthorTableCalcFields(TDataSet *DataSet) { AuthorTableFirstLast->AsString = AuthorTableFirst->AsString + " " + AuthorTableLast->AsString; } void TDMod::RefreshBookDetail() { BookDetailTable->Refresh(); } AnsiString TDMod::GetCurBook() { return BookDetailTable->FieldByName("Title")->AsString; } AnsiString TDMod::GetCurAuthor(void) { return AuthorTable->FieldByName("FirstLast")->AsString; } void TDMod::FindAuthor(AnsiString S) { AuthorTable->FindNearest(OPENARRAY(TVarRec, (S))); } void TDMod::FindTitle(AnsiString S) { AnsiString Temp(BookLookupTable->IndexName); BookLookupTable->IndexName = "idxTitle"; BookLookupTable->FindNearest(OPENARRAY(TVarRec, (S))); BookLookupTable->IndexName = Temp; } void TDMod::BookLookupInsert() { BookLookupTable->Insert(); } void TDMod::BookLookupPost() { if ((BookLookupTable->State == dsEdit)||(BookLookupTable->State == dsInsert)) BookLookupTable->Post(); } void TDMod::BookLookupCancel() { if ((BookLookupTable->State == dsEdit)||(BookLookupTable->State == dsInsert)) BookLookupTable->Cancel(); } void TDMod::BookLookupDelete() { BookLookupTable->Delete(); } Listing 11.2. Form1 gives you a look at both the Authors table and the Books table. A drop-down in dbGrid2 lets you view the lookup field. /////////////////////////////////////// // File: InsertEdit.cpp // Project: Lookup // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "Main.h" #include "DMod1.h" #include "InsertEdit.h" #pragma resource "*.dfm" TForm1 *Form1; __fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner) { } void __fastcall TForm1::Exit1Click(TObject *Sender) { Close(); } void __fastcall TForm1::EditBook1Click(TObject *Sender) { InsertEditForm->ShowEdit(DMod->CurBook); DMod->RefreshBookDetail(); } void __fastcall TForm1::NewBook1Click(TObject *Sender) { InsertEditForm->ShowInsert(); DMod->RefreshBookDetail(); } Listing 11.3. The InsertEditForm shows how to use DBLookupComboBoxes. /////////////////////////////////////// // File: InsertEdit.cpp // Project: Lookup // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "InsertEdit.h" #include "DMod1.h" #pragma resource "*.dfm" TInsertEditForm *InsertEditForm; __fastcall TInsertEditForm::TInsertEditForm(TComponent* Owner) : TForm(Owner) { } void __fastcall TInsertEditForm::bbInsertClick(TObject *Sender) { DMod->BookLookupTable->Insert(); } void __fastcall TInsertEditForm::bbPostClick(TObject *Sender) { DMod->BookLookupPost(); } void TInsertEditForm::ShowEdit(AnsiString S) { DMod->FindTitle(S); ShowModal(); DMod->BookLookupPost(); } void TInsertEditForm::ShowInsert() { DMod->BookLookupInsert(); DMod->BookLookupTableTitle->AsString = "My New Book"; ShowModal(); DMod->BookLookupPost(); } void __fastcall TInsertEditForm::CancelBtnClick(TObject *Sender) { DMod->BookLookupCancel(); } void __fastcall TInsertEditForm::DeleteBtnClick(TObject *Sender) { if (MessageBox(Handle, "Delete?" , "Delete Dialog", MB_YESNO) == ID_YES) DMod->BookLookupDelete(); } void __fastcall TInsertEditForm::FormShow(TObject *Sender) { TitleEdit->SetFocus(); } The Lookup program enables you to easily fill in the key fields of the Books table by looking them up in the Authors table. To understand why this capability is important, notice that the only way to tell which author is associated with which book is by placing the appropriate author number in the AuthNo field of the Book table. This is convenient from the point of view of the programmer who wants to construct a well-made relational database. In particular, it saves space by allowing the construction of one-to-many relationships. However, the user isn't going to want to have to remember that Herman Melville is associated with the number 2, Jack Kerouac with the number x, and so on. The point of a lookup field is that it lets you look up a list of authors in the author table, and then automatically assigns the chosen author number to the AuthNo field in the Books table. This program uses two tables called, not surprisingly, Author.db and Book.db. Both of these tables are found on the CD-ROM that accompanies this book. Tables 11.1 and 11.2 show the schema for the tables. Table 11.1. Author.db table structure. Name Type Keyed AuthNo AutoInc Key First Character(25) N/A Last Character(25) N/A Dates Character(25) N/A BirthPlace Character(25) N/A Table 11.2. Book.db table structure. Name Type Keyed BookNo AutoInc Key AuthNo LongInt Foreign Key Title Character (35) N/A NOTE: Notice the use of the AutoIncrement fields in the table definitions shown in Tables 11.1 and 11.2. These fields will automatically be filled in when the user adds a new record at runtime. For instance, when you add the first record to the Books table, it will automatically be given a BookNo of 1. The second record will automatically be given a BookNo of 2, and so on. AutoIncrement fields are read-only, and frequently there is no need to show them to the user at runtime. Furthermore, I use Referential Integrity to ensure that the AuthNo field properly binds to the Author table. In particular, it ensures that you cannot insert records into the Book table that are not properly related to the Authors table through the AuthNo field. Referential Integrity also ensures that the value of the AuthNo field is filled in automatically when you insert a new record into the Books table. To view the Referential Integrity, load the book table into the Database Desktop, choose Table | Info Structure, select Referential Integrity in the Table Properties, highlight the AuthNoRI rule, and press the Detail Info button. There is more on this subject in the next chapter, called "Understanding Relational Databases." There is little actual work required to construct this program. In particular, look over the source code shown earlier, and you will see that the only significant line of code in the whole program is the one for the OnCalcFields event. Other than that, it's just a matter of manipulating the visual tools. To get started, create a new application and add a data module to it. Set up the Authors and Books tables on the data module. Bring up the Fields Editor for both tables and create objects for all of their fields. Give the tables and data sources appropriate names, such as AuthorTable and BookLookupTable, as shown in Figure 11.7. Note that later on I will add a second in- stance of the Book table to the program so that I can simultaneously perform a lookup and a one-to-many. FIGURE 11.7. The TDataModule for the Lookup program. Inside the Author table, create a calculated field called LastFirst. To create the calculated field, first right-click the TTable object, and then right-click the Fields Editor and select New from the menu. After creating the calculated field, assign the following method to the OnCalcFields event: void __fastcall TDMod::AuthorTableCalcFields(TDataSet *DataSet) { AuthorTableFirstLast->AsString = AuthorTableFirst->AsString + " " + AuthorTableLast->AsString; } This field will be the one that is looked up in the second table. The issue here is that just looking up the last name of an author is not sufficient--you need to look up both first and last names in order to be sure you are finding a unique author. In other words, you can't tell Henry James from William James or Tom Wolfe from Thomas Wolfe unless you have both the first and last name present. It would be wasteful of disk space to permanently add a field to the table that combined the first and last names, but you can create a temporary copy of that field with a calculated field. Now that you have a calculated field in place, it is time to create a lookup field. To get started, bring up the Fields Editor for the Book table. Right-click it and create a new field called AuthorLookup. Set its Type to String and its Field Type to Lookup. The KeyField should be set to AuthNo, the Dataset to AuthorTable, the Lookup Key to AuthNo, and the Result field to LastFirst. Figure 11.8 shows how the New Field dialog should look when you are done. Notice that you can also fill in this same information in the Object Inspector if you first select the BookLookupTable object. (In other words, you could create a new object and then close the Fields Editor without specifying any of its properties. Later, you could select the object and designate its type, its lookup fields, and so on.) FIGURE 11.8. Filling in the New Field dialog. Go back to Form1 and make sure the two TDBGrids are arranged one above the other and are hooked up properly to the tables on the TDataModule. Run the application. The AuthorLookup field in the TDBGrid object associated with the Books table is now a drop-down combo box. If you click it once, and then drop down its list, you can then perform a lookup into the LastFirst field of the Author table. This lookup will automatically fill in the AuthNo field of the book table. You can use this lookup to insert a new author into a new record or to change the author of an existing record. Note that lookup fields give you two distinct benefits. They enable you to perform a "join" between the Books table and the Authors table, and they allow you to look up a reference in a drop-down list. The implementation of this program found on the CD-ROM that accompanies this book actually enables the user to perform the lookup on a second form. I implement things that way because it is probably easiest from the user's perspective, and because I want to support both a one-to-many relationship and a lookup between the Authors and Books tables. However, if you just want to see how lookups work, then you should follow the technique described previously. NOTE: The capability of having a drop-down list in a grid object comes for free in BCB, even when you are not doing lookups. Go back in design mode and open up the Columns property of a grid object. Add all the fields to the Columns list box. You can now select one of the fields, such as Title, and choose the PickList button in order to create a set of default values available for the field. The user can access these values at runtime by clicking the field and dropping down the combo box, per the lookup example discussed previously. This is the capability supported by the old TDBLookupList and TDBLookupCombo from the old Windows 3.1 days. Besides the TDBGrid object, there are two other controls in BCB that understand lookup fields. The first of these controls is shown on Form2 of the Lookup program found on the CD-ROM that accompanies this book. The TDBLookupComboBox is the default control you will get if you drag and drop the AuthorLookup field from the Fields Editor onto a form. If you perform the drag-and-drop operation, the control will be hooked up automatically. If you want to hook it up manually, just connect its DataSource to the dsBook object and its DataField to the AuthorLookup field. There is also a TDBLookupListBox, which works exactly the same way as the TDBLookupComboBox. NOTE: Both the TDBLookupListBox and TDBLookupComboBox have fields that correspond to the ones you filled in with the New Field dialog shown in Figure 11.7. However, there is no need to fill in these fields a second time. Just hook up the DataSource and DataFields properties, and you are ready to go. When you are working with the Lookup program found on the book's CD-ROM, you should note that Form1 does not contain a lookup. It's meant to help you scan through all the available data so you can grok the significance of the lookup process. The top part of the second form, called the InsertEditForm, is somewhat closer to the type of display you would want to present to the user in a real program. However, I have extended this form to include a TDBGrid object, just so you can see how the lookup combo box is inserted automatically into the grid. When working with the InsertEditForm, notice how easy it is to simply type in a new book name, select an author in the combo box, and then perform a Post by clicking the OK button. The process is very simple from the user's point of view. In particular, a new BookNo is being assigned automatically by the AutoIncrement field, and the new AuthNo is being filled in automatically by the lookup process. Here are the two ways to handle the data in InsertEditForm: InsertMode: A temporary book name appears in the TitleEdit control when you bring up the form in this mode. Type in the name of the book you want to record in the Title field, and then drop down the AuthorLookup combo to select an author. EditMode: There is usually data in both the Title and AuthorLookup fields when you bring up the mode in this form. The user can either edit the Title field, or associate the book with a new author. I call a method called FindTitle in the DMod module to make sure that the form shows the record the user wants to edit. It's important to note that lookup controls probably would not be appropriate for use with big datasets because drop-down controls aren't very handy for displaying thousands of items. Even list boxes are fairly limited in these circumstances. You would therefore use lookups mostly with smaller datasets. It should also be pointed out that not being able to use both one-to-many relationships and lookups between the same two tables is a significant inconvenience. However, the Lookup example discussed in these sections and implemented on the book's CD-ROM shows that the workaround is not that complex. NOTE: If you are concerned that the solution to the problem in the last paragraph requires using three TTable objects instead of only two, I would ask you to recall that the goal of this book is to show how to get things done, not how to do things in the smallest possible space. If you can get things done in five minutes through a technique that you know is bug free, that is something you should give up only reluctantly, particularly if the alternative is working for days or weeks to implement a solution that is likely to have bugs that will take another week or two to squash. At the very least, you should implement the quick solution for the first draft of your application, and then come back and look for optimizations once you have a working version of the product. Don't ever try to optimize during your first draft of an application! There is no such thing as getting things right the first time in programming. Instead, you should implement a reasonable solution, critique it, come back and make improvements, critique the improvements, make another pass over the application, and so on. This kind of cycle demands that you not get too hung up on optimizations during early drafts, because you are likely to find that any one part of the application will change in future revisions. If you have the thing implemented correctly, and there is still time left in the project cycle, then you can come back and seek to optimize the code! The final kicker in this analysis is that contemporary application programmers rarely have time to optimize. Given the success rate of most projects, your customers or managers will usually be ecstatic if you just turn in a working solution to the problem on time. If you release the same application 10 percent faster and 20 percent smaller, but six months later, it's unlikely you will win quite the same number of kudos you think you deserve. I usually leave the minute optimizations up to the development teams at Borland. They know how to reach into the fire without getting burned. If you want to change the author associated with a particular record, you just click a new item in the list box. The author number will be changed automatically for you by the lookup. It's all very simple and intuitive when viewed from the user's perspective. TDBGrid at Runtime TDBGrid objects can be completely reconfigured at runtime. You can hide and show columns, change the order of columns, the color of columns, the color of rows, the color of fields, and the width of columns. The GridTricks program, shown in Figure 11.9, demonstrates how to take a TDBGrid through its paces at runtime. The program is fairly straightforward except for two brief passages. The first passage involves creating checkbox controls on-the-fly, and the second shows how to change the traits of columns. FIGURE 11.9. The main GridTricks program enables you to change the appearance of a grid at runtime. You need a color monitor to really see what is happening. When the user wants to decide which fields are visible, GridTricks pops up a second form and displays the names of all the fields from the ORDERS table in a series of checkboxes. The user can then select the fields that he or she wants to make visible. The selected checkboxes designate fields that are visible, whereas the nonselected ones represent invisible fields. The program also enables you to set the order and width of fields, as well as to hide and show the titles at the top of the grid. (See Listings 11.1 and 11.2.) The code for the GridTricks program is in the CHAP17 directory on this book's CD-ROM. (See Listings 11.4-11.8.) Listing 11.4. The main unit for the GridTricks Program. /////////////////////////////////////// // File: Main.cpp // Project: GridTricks // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "Main.h" #include "DMod1.h" #include "NamesDlg.h" #include "ColumnEditor1.h" #include "ShowOptions1.h" #define NEWCOLOR clGreen #pragma resource "*.dfm" TForm1 *Form1; __fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner) { } void __fastcall TForm1::Exit1Click(TObject *Sender) { Close(); } void __fastcall TForm1::FieldNames1Click(TObject *Sender) { NamesDialog->ShowNames(fdFieldNames); } void __fastcall TForm1::FieldObjectNames1Click(TObject *Sender) { NamesDialog->ShowNames(fdObjectNames); } /////////////////////////////////////// // DBGrid1DrawColumnCell // Paints ROW different color depending on value of ItemsTotal field /////////////////////////////////////// void __fastcall TForm1::DBGrid1DrawColumnCell(TObject *Sender, const TRect &Rect, Integer DataCol, TColumn *Column, TGridDrawState State) { if (ColorRows1->Checked) { if (DMod->OrdersTableItemsTotal->Value < 1000) DBGrid1->Canvas->Font->Color = clRed; else if (DMod->OrdersTableItemsTotal->Value < 10000) DBGrid1->Canvas->Font->Color = clBlue; else DBGrid1->Canvas->Font->Color = clGreen; } DBGrid1->DefaultDrawColumnCell(Rect, DataCol, Column, State); } void TForm1::ColorTitles(BOOL UseDefaultColor) { TColor Colors[] = {clRed, clBlue, clGreen, clLime, clWhite, clFuchsia}; int i; for (i = 0; i < DBGrid1->Columns->Count; i++) { TColumn *Column = DBGrid1->Columns->Items[i]; TColumnTitle *ColumnTitle = Column->Title; if (UseDefaultColor) ColumnTitle->Font->Color = FDefaultColor; else ColumnTitle->Font->Color = Colors[random(7)]; } } void __fastcall TForm1::AnimateTitles1Click(TObject *Sender) { Timer1->Enabled = (!Timer1->Enabled); AnimateTitles1->Checked = Timer1->Enabled; if (!AnimateTitles1->Checked) ColorTitles(True); } void __fastcall TForm1::ColorRows1Click(TObject *Sender) { ColorRows1->Checked = (!ColorRows1->Checked); DBGrid1->Repaint(); } void __fastcall TForm1::MarkColumnClick(TObject *Sender) { MarkColumn->Checked = (!MarkColumn->Checked); TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; if (MarkColumn->Checked) { Column->Font->Color = NEWCOLOR; Column->Font->Style = TFontStyles() << fsBold; } else { Column->Font->Color = FDefaultColor; Column->Font->Style = TFontStyles(); } HandleCaption(); } /////////////////////////////////////// // Handle Caption /////////////////////////////////////// void TForm1::HandleCaption() { TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; AnsiString S(DBGrid1->SelectedIndex); Caption = S; if (Column->Font->Color == FDefaultColor) Caption = "Column " + S + " is Default"; else Caption = "Column " + S + " is Marked"; } void __fastcall TForm1::DBGrid1ColEnter(TObject *Sender) { TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; MarkColumn->Checked = (Column->Font->Color == NEWCOLOR); HandleCaption(); } void __fastcall TForm1::FormCreate(TObject *Sender) { FDefaultColor = DBGrid1->Font->Color; HandleCaption(); } void __fastcall TForm1::Timer1Timer(TObject *Sender) { ColorTitles(False); } void __fastcall TForm1::ShowFieldEditor1Click(TObject *Sender) { ColumnEditor->ShowColumns(); } void __fastcall TForm1::ToggleTitles1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgTitles)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgTitles; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgTitles; } void __fastcall TForm1::ToggleIndicator1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgIndicator)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator; } void __fastcall TForm1::ShowTitlesIndicator1Click(TObject *Sender) { ShowTitlesIndicator1->Checked = !(TDBGridOptions(DBGrid1->Options).Contains(dgIndicator) && TDBGridOptions(DBGrid1->Options).Contains(dgTitles)); if (ShowTitlesIndicator1->Checked) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator << dgTitles; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator >> dgTitles; DBGrid1->Refresh(); } void __fastcall TForm1::ColLines1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgColLines)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgColLines; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgColLines; ColLines1->Checked = DBGrid1->Options.Contains(dgColLines); } void __fastcall TForm1::RowLines1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgRowLines)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgRowLines; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgRowLines; RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines); } void __fastcall TForm1::ShowAllOptions1Click(TObject *Sender) { ShowOptionsForm->ShowOptions(DBGrid1->Options); } void __fastcall TForm1::MustPressF2orEntertoEdit1Click(TObject *Sender) { DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgAlwaysShowEditor; MustPressF2orEntertoEdit1->Checked = !TDBGridOptions(DBGrid1->Options).Contains(dgAlwaysShowEditor); } void __fastcall TForm1::ChangeWidthofField1Click(TObject *Sender) { AnsiString S(""); TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; if (InputQuery("Data Needed", "New Width of Selected Field", S)) Column->Width = S.ToInt(); } void __fastcall TForm1::HideCurrentColumn1Click(TObject *Sender) { if (MessageBox(Handle, "Hide Column?", "Hide Info?", MB_YESNO | MB_ICONQUESTION) == ID_YES) { TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; Column->Free(); } } void __fastcall TForm1::MoveCurrentColumn1Click(TObject *Sender) { AnsiString S(""); if (InputQuery("Data Needed", "Enter new position of column", S)) { DMod->OrdersTable->Fields[DBGrid1->SelectedIndex]->Index = S.ToInt(); } } Listing 11.5. The data module for the GridTricks program. /////////////////////////////////////// // File: ColumnEditor1.cpp // Project: GridTricks // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "DMod1.h" #pragma resource "*.dfm" TDMod *DMod; __fastcall TDMod::TDMod(TComponent* Owner) : TDataModule(Owner) { } void TDMod::GetFieldNames(TStringList *Items) { int i; Items->Clear(); for (i = 0; i < OrdersTable->FieldCount - 1; i++) Items->Add(OrdersTable->Fields[i]->FieldName); } void TDMod::GetObjectNames(TStringList *Items) { int i; Items->Clear(); for (i = 0; i < OrdersTable->FieldCount - 1; i++) Items->Add(OrdersTable->Fields[i]->Name); } Listing 11.6. The Column Editor for the GridTricks program. /////////////////////////////////////// // File: ColumnEditor1.cpp // Project: GridTricks // Copyright (c) 1997 by Charlie Calvert // The last build of BCB I checked this on before shipping // was still broken. BCB was not properly updating the // data on the grids. Hopefully this program will start // working properly once there is an update for BCB 1.0. // The code I have here works fine in Delphi. In short, the // problem is not in my code, and its not in the VCL. Its a // BCB problem. // // Check my website for updates: users.aol.com/charliecal // #include <vcl\vcl.h> #pragma hdrstop #include "ColumnEditor1.h" #include "DMod1.h" #include "Main.h" #pragma resource "*.dfm" #define GAP 2 TColumnEditor *ColumnEditor; //-------------------------------------------------------------------------- __fastcall TColumnEditor::TColumnEditor(TComponent* Owner) : TForm(Owner) { } void TColumnEditor::CreateCheckBox(int Index, AnsiString Name, BOOL Visible) { CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this); CheckBoxAry[Index]->Parent = ColumnEditor; CheckBoxAry[Index]->Caption = Name; CheckBoxAry[Index]->Left = 10; CheckBoxAry[Index]->Top = Index * (CheckBoxAry[Index]->Height + GAP); CheckBoxAry[Index]->Width = 200; CheckBoxAry[Index]->Checked = Visible; } void TColumnEditor::ShowColumns(void) { int i; TColumn *Column; for (i = 0; i < DMod->OrdersTable->FieldCount; i++) CreateCheckBox(i, DMod->OrdersTable->Fields[i]->Name, DMod->OrdersTable->Fields[i]->Visible); Height = (DMod->OrdersTable->FieldCount - 1) * (CheckBoxAry[0]->Height + GAP); if (Height > 470) Height = 470; ShowModal(); for (i = 0; i < DMod->OrdersTable->FieldCount; i++) DMod->OrdersTable->Fields[i]->Visible = CheckBoxAry[i]->Checked; } Listing 11.7. The NamesDlg for the GridTricks program. /////////////////////////////////////// // File: NamesDlg.cpp // Project: GridTricks // Copyright (c) 1997 by Charlie Calvert #include <vcl\vcl.h> #pragma hdrstop #include "NamesDlg.h" #include "DMod1.h" #pragma resource "*.dfm" TNamesDialog *NamesDialog; __fastcall TNamesDialog::TNamesDialog(TComponent* Owner) : TForm(Owner) { } void TNamesDialog::ShowNames(TFieldData FieldData) { switch(FieldData) { case fdFieldNames: DMod->GetFieldNames((TStringList *)ListBox1->Items); break; case fdObjectNames: DMod->GetObjectNames((TStringList *)ListBox1->Items); break; } Show(); } void __fastcall TNamesDialog::BitBtn1Click(TObject *Sender) { Close(); } Listing 11.8. The ShowOptions module shows which DBGrid options are currently active. #include <vcl\vcl.h> #pragma hdrstop #include "ShowOptions1.h" #pragma resource "*.dfm" TShowOptionsForm *ShowOptionsForm; __fastcall TShowOptionsForm::TShowOptionsForm(TComponent* Owner) : TForm(Owner) { int i; int j = 0; for (i = 0; i < ComponentCount; i++) if (dynamic_cast<TCheckBox*>(Components[i])) { CheckBox[j] = (TCheckBox*)Components[i]; j++; } } void TShowOptionsForm::ShowOptions(TDBGridOptions Options) { int i; for (i = 0; i < 12; i++) if (Options.Contains(i)) CheckBox[i]->Checked = True; else CheckBox[i]->Checked = False; ShowModal(); } In the next few paragraphs you will find descriptions of the key parts of the GridTricks program. Understanding its constituent parts will help you to take control over the grids you display in your programs. Most of the code in the GridTricks program is fairly simple. However, the program performs a number of separate tasks. To grasp the program, it's necessary to divide and conquer; that is, to take the tasks performed by the program one at a time. Find out how each one works, and then move on to the next one. If you proceed in this fashion, you will find the program easy to understand. Controlling the Options Property of a DBGrid at Runtime You can use the Options field of a TDBGrid to change its appearance. The Options property has the following possible values: dgEditing Set to True by default, it enables the user to edit a grid. You can also set the grid's ReadOnly property to True or False. dgTitles Designates whether titles can be seen. dgIndicator Determines whether to show the small icons on the left of the grid. dgColumnResize Designates whether or not the user can resize columns. dgColLines Determines whether or not to show the lines between columns. dgRowLines Designates whether or not to show the lines between rows. dgTabs Enables the user to tab and Shift+tab between columns. dgAlwaysShowEditor If you select a field will you be in Edit mode auto-matically? dgRowSelect Can select rows, mutually exclusive with dgAlwaysShowEditor. dgAlwaysShowSelection Selection remains even when grid loses focus. dgConfirmDelete Shows message box when user presses Ctrl+Delete. dgCancelOnExit Cancels Inserts on exit if no changes were made to row. dgMultiSelect Can select multiple noncontiguous rows with Ctrl+Click. Here is the declaration for the enumerated type where these values are declared: enum TDBGridOption { dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit, dgMultiSelect }; For instance, you can set the options at runtime by writing code that looks like this: DBGrid1->Options = TDBGridOptions() << dgTitles; This code in effect turns all the options to False except dgTitles. This code turns off all options but dgTitles and dgIndicator. DBGrid1->Options = TDBGridOptions() << dgTitles << dgIndicator; More specifically, the code sets the DBGrid1 Options property to a set that contains only dgTitles and dgIndicator. This code toggles dgTitles and dgIndicator off and on each time it is called: void __fastcall TForm1::ToggleTitles1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgTitles)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgTitles; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgTitles; } The set operators shown in ToggleTitles1Click move the dgTitles option in and out of DBGrid->Options. The following code shows how to toggle back and forth between showing both indicators and titles and hiding both indicators and titles: void __fastcall TForm1::ShowTitlesIndicator1Click(TObject *Sender) { ShowTitlesIndicator1->Checked = !(TDBGridOptions(DBGrid1->Options).Contains(dgIndicator) && TDBGridOptions(DBGrid1->Options).Contains(dgTitles)); if (ShowTitlesIndicator1->Checked) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgIndicator << dgTitles; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgIndicator >> dgTitles; DBGrid1->Refresh(); } This code moves both the dgIndicator and dgTitles elements in and out of the Options array as needed. The << operator adds elements to a set, while the >> operator moves things out of the set. If you need to move multiple elements in and out of the set, just use the operator multiple times as shown in the ShowTitlesIndicator1Click method. NOTE: The following standard set operations will not work with the Options property because the += and -= operators do not work with sets that are properties because of the extra work involved in calling get and set methods: TDBGridOptions Options; Options << dgTitles << dgIndicator; DBGrid1->Options += Options; DBGrid1->Options -= Options; Here is an example of code showing how to toggle the dgRowLines element of the Options property on and off at runtime. void __fastcall TForm1::RowLines1Click(TObject *Sender) { if (DBGrid1->Options.Contains(dgRowLines)) DBGrid1->Options = TDBGridOptions(DBGrid1->Options) >> dgRowLines; else DBGrid1->Options = TDBGridOptions(DBGrid1->Options) << dgRowLines; RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines); } The last line of code in the routine toggles the check mark before the RowLines menu item so that it reflects the current state of the grid. In other words, if dgRowLines is part of the set, the menu item will be checked; if it is not part of the set, the menu item will not be checked: RowLines1->Checked = DBGrid1->Options.Contains(dgRowLines); In this section you have seen how to toggle the elements of the Options set back and forth at runtime. Most of the code for doing this is fairly simple, although you need to have a basic grasp of BCB set operations to understand how it works. If you need to brush up on this material, sets were covered in more depth in Chapter 2, "Basic Facts About C++Builder." Displaying the DBGrid Options at Runtime Now that you know how to toggle the Options of a DBGrid, it might be worthwhile spending a few moments learning how to display the Options to the user at runtime. As shown in Figure 11.10, I use a set of 12 CheckBoxes to depict the current state of 12 DBGrid options. In the next few paragraphs I will explain how the code that drives this form works. FIGURE 11.10. Using checkboxes to depict the available DBGrid options to the user at runtime. In the header file for the unit, I declare an array of checkboxes: TCheckBox *CheckBox[12]; I initialize these checkboxes in the constructor for the form: __fastcall TShowOptionsForm::TShowOptionsForm(TComponent* Owner) : TForm(Owner) { int i; int j = 0; for (i = 0; i < ComponentCount; i++) if (dynamic_cast<TCheckBox*>(Components[i])) { CheckBox[j] = (TCheckBox*)Components[i]; j++; } } This code iterates over all the components on the form checking for ones that are of type TCheckBox. When it finds one, it adds it to the array of CheckBoxes. The code uses dynamic_cast to check whether each item in the Components array is of type TCheckBox. NOTE: A Components array is implemented in TComponent and is maintained automatically for all components that descend from TComponent. The concept of ownership is what governs which items are put in the Components array. All components that are owned by the form are automatically, and by definition, included in the Components array for the form. In other words, if you drop a component on a form, it will be listed in the Components array for the form. You can use the ComponentCount property of the form to determine how many items are in the Components array. After filling in the array of checkboxes, it is a simple matter to toggle the Checked property of each checkbox depending on the current state of each DBGrid option: void TShowOptionsForm::ShowOptions(TDBGridOptions Options) { int i; for (i = 0; i < 12; i++) if (Options.Contains(TDBGridOption(i))) CheckBox[i]->Checked = True; else CheckBox[i]->Checked = False; ShowModal(); } This code determines which items in the DBGridOptions set are turned on, and then toggles the appropriate checkbox. The code depends, of course, on the fact that the DBGridOptions set is a list of items with values ranging from 0 to 11. To understand this code, you must grasp that DBGridOption is an enumerated type, and DBGridOptions is a set ranging over the values in that enumerated type, with dgEditing being the minimum value and dgMultiSelect being the maximum value: enum TDBGridOption {dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit, dgMultiSelect}; typedef Set<TDBGridOption, dgEditing, dgMultiSelect> TDBGridOptions; Changing the Colors and Fonts in a Grid The next three sections of the chapter cover changing the colors of all the titles, columns, rows, and even individual cells in a TDBGrid. This is not something you have to do all that often, but when the need comes around it is fairly pressing. Before reading these sections, you should be sure to run the GridTricks program, because it will be hard to read the code without some understanding of what it does. Changing the Titles in a TDBGrid Object Here is how to color the titles in a TDBGrid: void TForm1::ColorTitles(BOOL UseDefaultColor) { TColor Colors[] = {clRed, clBlue, clGreen, clLime, clWhite, clFuchsia}; int i; for (i = 0; i < DBGrid1->Columns->Count; i++) { TColumn *Column = DBGrid1->Columns->Items[i]; TColumnTitle *ColumnTitle = Column->Title; if (UseDefaultColor) ColumnTitle->Font->Color = FDefaultColor; else ColumnTitle->Font->Color = Colors[random(7)]; } } This code first declares an array of colors. The constants seen here are pre-declared colors of type TColor. The actual number of colors in the array was chosen at random. I could have added or subtracted colors from the array without changing the rest of code in the routine, with the exception of the number 7, which is passed to random in the routine's last line of code. The TColumn object defines how a column in a TDBGrid should look. That is, it defines the font, color, and width of the column. The Columns property of a TDBGrid is of type TDBGridColumns, which is a collection of TColumn objects. Each TColumn object has a title. This title is defined in an object of type TColumnTitle. Finally, a TColumnTitle has color, font, and caption properties: TDBGrid Object Columns Property TColumn Object TColumnTitle Font, Color, Caption The preceding list is not an object hierarchy, but just a way of illustrating the relationship between these different entities. In other words, the grid object contains a Columns property, and the Columns property contains TColumn objects, and the TColumn object contains a TColumnTitle, which in turn contains a Font, Color, and Caption. To get hold of a TColumn object, you can use the Items property of TDBGridColumns: TColumn *Column = DBGrid1->Columns->Items[i]; To move from there to a TColumnTitle object, you can use the Title property of a TColumn object: TColumnTitle *ColumnTitle = Column->Title; Once the preceding ColorTitles method has the ColumnTitle in its hands, it can set it to whatever color it wants: if (UseDefaultColor) ColumnTitle->Font->Color = FDefaultColor; else ColumnTitle->Font->Color = Colors[random(7)]; The FDefaultColor variable is of type TColor. In the OnCreate event for the form, I set it to the default color for the grid's font: FDefaultColor = DBGrid1->Font->Color; Phew! That was the hard one. If you understand what has happened here, you will have no trouble with the next two sections, which cover changing the color of columns and rows in a grid. Changing an Entire Column in a Grid If you understand the code in the last section, it will be easy to understand how to change the look of a single column. Writing this kind of code will enable you to emphasize a certain part of a dataset or to bring the users eye to certain part of your form. Here is the method that changes the appearance of a column in a TDBGrid: void __fastcall TForm1::MarkColumnClick(TObject *Sender) { MarkColumn->Checked = (!MarkColumn->Checked); TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; if (MarkColumn->Checked) { Column->Font->Color = NEWCOLOR; Column->Font->Style = TFontStyles() << fsBold; } else { Column->Font->Color = FDefaultColor; Column->Font->Style = TFontStyles(); } HandleCaption(); } This code first grabs hold of a selected column in a grid: TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; If the user has indicated that he wants this column to stand out, it is a simple matter to change its color and set its font to bold: Column->Font->Color = NEWCOLOR; Column->Font->Style = TFontStyles() << fsBold; Notice that the Style property is a set, and so you use a template class to manipulate its members. Here, as found in GRAPHICS.HPP, are the different styles you can associate with a font: enum TFontStyle { fsBold, fsItalic, fsUnderline, fsStrikeOut }; There are some parts of the code, such as MarkColumn and HandleCaption, that I don't mention. I ignore these elements because they are merely part of the logic of this program and are not germane to the subject of changing an individual column. Changing the Color of a Row in a Grid In the last two sections on columns and column titles, you have been working with the TColumn object. You can also change the color of the text in a TDBGrid by working with the font associated with the grid's TCanvas object: void __fastcall TForm1::DBGrid1DrawColumnCell(TObject *Sender, const TRect &Rect, Integer DataCol, TColumn *Column, TGridDrawState State) { if (ColorRows1->Checked) { if (DMod->tblOrdersItemsTotal->Value < 1000) DBGrid1->Canvas->Font->Color = clRed; else if (DMod->tblOrdersItemsTotal->Value < 10000) DBGrid1->Canvas->Font->Color = clBlue; else DBGrid1->Canvas->Font->Color = clGreen; } DBGrid1->DefaultDrawColumnCell(Rect, DataCol, Column, State); } If you run the GridTricks program, you can see the effect of this code by choosing Color Rows from the Options menu of that program. Be sure that none of the other special effects are turned on when you choose this option, because they can interfere with your ability to see its results. Be sure to scroll the grid up and down after turning the effect on, because the data at the top of the grid is fairly homogenous. The data shown in the grid is from the Orders table in the BCDEMOS database. The code shown here colors each row in the grid according to the amount of money reported in the ItemsTotal field of the Orders table. For instance, if the ItemsTotal field contains a sum less than $1,000 dollars, that row is painted Red: DBGrid1->Canvas->Font->Color = clRed; Here the code sets the font of the TCanvas object for the grid to clRed. Nothing could be simpler. Changing the Width of a Column The user can change the width of a column at runtime with the mouse. But how can you do the same thing programmatically without any input from the user? If you want to change the width of a column at runtime, just change the DisplayWidth property of the appropriate TField object: TblOrders->FieldByName("CustNo")->DisplayWidth = 12; TblOrdersCustNo->DisplayWidth = 12; The value 12 refers to the approximate number of characters that can be displayed in the control. Various factors, such as whether or not you are using a fixed-pitch font, affect the interpretation of this value. See the online help for additional information. Here is how you can change the width of the column in the grid without affecting the properties of the underlying field: void __fastcall TForm1::ChangeWidthofField1Click(TObject *Sender) { AnsiString S(""); TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; if (InputQuery("Data Needed", "New Width of Selected Field", S)) Column->Width = S.ToInt(); } This code asks the user for the width he or she wants to assign to the currently selected column. The code then makes the change by retrieving the column and changing its Width property. Hiding or Moving Columns in a TDBGrid The user can change the order of columns in a TDBGrid simply by clicking them and dragging them with a mouse. But how do you proceed if you want to do the same thing at runtime without the user's direct input? If you want to hide a field at runtime, you can set its Visible property to False: OrdersTable->FieldByName("CustNo")->Visible = False; OrdersTableCustNo->Visible = False; NOTE: This code will not work in the first version of BCB. See the top of the ColumnEditor1.cpp file for late-breaking information on this process. Both lines of code perform identical tasks. To show the fields again, simply set Visible to True. Alternatively, you can retrieve a TColumn object from the grid, and then quietly dispose of it: void __fastcall TForm1::HideCurrentColumn1Click(TObject *Sender) { if (MessageBox(Handle, "Hide Column?", "Hide Info?", MB_YESNO | MB_ICONQUESTION) == ID_YES) { TColumn *Column = DBGrid1->Columns->Items[DBGrid1->SelectedIndex]; Column->Free(); } } That'll do it! The column disappears from the grid once it has been freed. Alternatively, you can set the width of a column to 0, which makes the column itself go away, but not the lines between columns. In order to allow the user to decide which fields are visible, GridTricks pops up a second form with a series of checkboxes on it. The program actually creates each of these checkboxes at runtime. In other words, it doesn't just pop up a form with the correct number of checkboxes on it, but instead iterates through the TblOrders object, finds out how many checkboxes are needed, and then creates them dynamically at runtime. To perform these tasks, GridTricks calls on a form that is specially designed to display the checkboxes: void __fastcall TForm1::ShowFieldEditor1Click(TObject *Sender) { ColumnEditor->ShowColumns(); DMod->tblOrders->Refresh(); } The ShowColumns method of the VisiForm first calls a routine called CreateCheckBox that creates the checkboxes, displays the form, and finally sets the state of the checkboxes: void TColumnEditor::ShowColumns(void) { int i; TColumn *Column; for (i = 0; i < DMod->OrdersTable->FieldCount; i++) CreateCheckBox(i, DMod->OrdersTable->Fields[i]->Name, DMod->OrdersTable->Fields[i]->Visible); Height = (DMod->OrdersTable->FieldCount - 1) * (CheckBoxAry[0]->Height + GAP); if (Height > 470) Height = 470; ShowModal(); // This is the code that does not work in BCB 1.0 for (i = 0; i < DMod->OrdersTable->FieldCount; i++) DMod->OrdersTable->Fields[i]->Visible = CheckBoxAry[i]->Checked; } The ShowColumns method iterates through the Query1 object and assigns one checkbox to each field. It also asks TQuery for the names of the fields, and determines whether or not each field is currently hidden or visible. Here is the code that creates a checkbox on-the-fly: void TColumnEditor::CreateCheckBox(int Index, AnsiString Name, BOOL Visible) { CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this); CheckBoxAry[Index]->Parent = ColumnEditor; CheckBoxAry[Index]->Caption = Name; CheckBoxAry[Index]->Left = 10; CheckBoxAry[Index]->Top = Index * (CheckBoxAry[Index]->Height + GAP); CheckBoxAry[Index]->Width = 200; CheckBoxAry[Index]->Checked = Visible; } Most of the code in this example is performing relatively mundane tasks such as assigning names and locations to the checkboxes. These are the two key lines: CheckBoxAry[Index] = (TCheckBox*) new TCustomCheckBox(this); CheckBoxAry[Index]->Parent = ColumnEditor; The first line actually creates the checkbox and gives it an owner. The second line assigns a parent to the checkbox. NOTE: The difference between a parent and an owner can be confusing at times. A form is always the owner of the components that reside inside it. As such, it is responsible for allocating and deallocating memory for these components. A form might also be the parent of a particular component, which means that Windows will ensure the component will be displayed directly on the form. However, one component might also find that another component is its parent, even though both components are owned by the form. For instance, if you place a TPanel on a form and then two TButtons on the TPanel, all three components will be owned by the form; however, the buttons will have the panel as a parent, whereas the TPanel will have the form as a parent. Ownership has to do with memory allocation. Parenthood usually describes what surface a component will be displayed on. Ownership is a BCB issue--parenthood is mostly a Windows API issue. In particular, it's Windows that cares about parenting, and it's Windows that handles that actual drawing of the controls. If you get confused about this while in the midst of a lengthy programming session, you can look it up in the online help by searching on the topic Parent. The grids supplied with the first version of BCB are reasonably flexible objects that perform most of the tasks required of them. If you feel you need some additional functionality, check with third-party tool makers such as TurboPower software. A number of third-party grids with extended capabilities are available on the market, and some of them are well worth the purchase price. New features of the TDuBGrid object not found in BCB 1.0 include the capability to add combo boxes to the grid, and to color the columns of your grids. Moving Columns at Runtime To move the location of a column at runtime, you can simply change its index, which is a zero-based number: DMod->OrdersTable->FieldByName("CustNo")->Index = 0; DMod->OrdersTable->FieldByName("CustNo")->Index = 2; By default, the CustNo field in the Orders table is at the second position, which means its index is 1. The code in the first example moves it to the first position, whereas the code that reads Query1CustNo.Index = 2; moves it to the third position. Remember, the Index field is zero-based, so moving a field to Index 1 moves it to the second field in a record. The first field is at Index 0. When you change the index of a field, you do not need to worry about the indexes of the other fields in a record; they will be changed automatically at runtime. That is all I'm going to say about DBGrid objects. I've gone on at considerable length about this one component, but this is one of the tools that lie at the heart of many database programs, and it's therefore worthy of a fairly serious look. Multirecord Objects Another object that deserves mention is the TDBCtrlGrid, shown in Figure 11.11. You can use this object to view multiple records from a single table at one time without using the TDBGrid component. In other words, you can drop down TDBEdit controls onto a TDBCtrlGrid, and these edit controls will automatically be duplicated in a series of rows, where the first set of controls shows the first record, the second set the second record, and so on. You only have to drop down one set of controls--the extra sets are duplicated for you automatically by the DBCtrlGrid. To get started with this object, drag and drop the Country table off the Explorer. Delete the TDBGrid object created for you automatically by BCB, and add the TDBCtrlGrid object off the Data Controls page of the Component Palette. Use the Fields Editor to drag and drop all the fields from Country table onto the top section of the TDBCtrlGrid. Arrange them as shown in Figure 11.11. If you need help getting everything arranged properly, notice that TDBCtrlGrids have RowCount and ColCount properties that enable you to define the number of rows and columns in the object. In this case, I have set the RowCount to 7. FIGURE 11.11. The TDBCtrlGrid object on the form of the CtrlGrid application. When displaying a grid on a form, it often helps to add a DBNavigator control to the form so the user can easily iterate through records without using the scrollbar. I like to place the DBNavigator on a panel at the bottom of the form. When arranging the grid and other components on the form at runtime, it sometimes helps to respond to the OnResize event for the form: void __fastcall TForm1::FormResize(TObject *Sender) { Grid->Left = 0; Grid->Top = 0; Grid->Width = ClientWidth; Grid->Height = ClientHeight - Panel1->Height; } The code shown here arranges the grid so that it reaches from the top left of the form, all the way to the right of the form and down to the top of the panel on which the DBNavigator resides. The preceding few simple lines of code will automatically be called whenever the form is resized by the user, thereby guaranteeing that all the components stay properly arranged. The DBCtrlGrid component doesn't bring any new functionality to BCB. It's useful, however, because it eliminates the need to have the user slide the scrollbar back and forth on a TDBGrid object. In other words, the TDBGrid object sometimes forces you to use the scrollbar in order to view all the fields of a record. That can be inconvenient, but the ability to view multiple records at once is so valuable that users are willing to put up with the minor annoyance of the scrollbar. The point of the TDBCtrlGrid object is that it lets you view multiple records at one time, while eliminating the need to scroll back and forth when viewing the data. It's hardly earth- shattering in its importance, but it can be very useful under some circumstances. It's a way to make the presentation of your data potentially more viable to the user. Summary In this chapter you learned some fairly sophisticated methods for displaying the data from multiple tables. In particular, you saw how BCB handles the key features of a relational database. The tools discussed in this chapter include the following: The Fields Editor The Query by Example tool in the DBD The components discussed in this chapter include the following: TField descendant objects The TDBCtrlGrid, TDBLookupComboBox, and TDBLookupListBox objects The DBGrid and DBCtrlGrid components The properties discussed in this chapter include the following: Calculated fields Lookup fields Good database programmers will find that there is a considerable amount of hidden power in the TField object and in the Fields Editor, as well as the other tools and components mentioned in this chapter.

©Copyright, Macmillan Computer Publishing. All rights reserved.